package com.lyswkjxgs.report.service;

import com.lyswkjxgs.report.domain.ConfigDTO;
import com.lyswkjxgs.report.domain.ReportParam;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.ByteArrayOutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.*;

@Service
public class ReportService {

    @Autowired
    private ConfigService configService;
    public String testConfig(ConfigDTO configDTO) {
        Connection connection = null;
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            String jdbcUrl = configDTO.getJdbcUrl();
            String username = configDTO.getUsername();
            String password = configDTO.getPassword();

            connection = DriverManager.getConnection(jdbcUrl, username, password);
        }
        catch (Exception e){
            return e.getMessage();
        }
        finally {
            try {
                if (Objects.nonNull(connection)){
                    connection.close();
                }
            } catch (SQLException e) {
                //
            }
        }
        return null;
    }

    public List<Map<String, Object>> getReport(ReportParam reportParam) {
        ConfigDTO configDTO = configService.getConfig();
        if (StringUtils.isBlank(configDTO.getJdbcUrl())){
            throw new RuntimeException("数据库未配置");
        }
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            String jdbcUrl = configDTO.getJdbcUrl();
            String username = configDTO.getUsername();
            String password = configDTO.getPassword();
            String sql = "select a.linename , sum(t.operno) - floor(sum(opermn)) " +
                    "  from ST_PAYMENT_CONSUM_DAY t, base_busline a " +
                    " where t.acccode in (208, 209) " +
//                    "   and t.statdate >= '"+reportParam.getStartDate()+"' " +
//                    "   and t.statdate < '"+reportParam.getEndDate()+"' " +
                    "   and t.statdate >= to_date('"+reportParam.getStartDate()+"', 'yyyymmdd') " +
                    "   and t.statdate < to_date('"+reportParam.getEndDate()+"', 'yyyymmdd') " +
                    "   and t.dptcode <> 028 " +
                    "   and t.buslineid = a.lineid " +
                    " group by a.linename " +
                    " order by a.linename";
            connection = DriverManager.getConnection(jdbcUrl, username, password);
            connection.setAutoCommit(true);
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            List<Map<String, Object>> result = new ArrayList<>();
            while (resultSet.next()){
                Map<String, Object> item = new HashMap<>();
                item.put("lineNo", resultSet.getString(1));
                item.put("takeCount", resultSet.getBigDecimal(2));
                result.add(item);
            }
            return result;
        }
        catch (Exception e){
            throw new RuntimeException(e);
        }
        finally {
            try {
                if (Objects.nonNull(resultSet)){
                    resultSet.close();
                }
            } catch (SQLException e) {
                //ignore
            }
            try {
                if (Objects.nonNull(preparedStatement)){
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                //ignore
            }
            try {
                if (Objects.nonNull(connection)){
                    connection.close();
                }
            } catch (SQLException e) {
                //
            }
        }

    }

    public void export(ReportParam reportParam, HttpServletResponse response) throws Exception{
        List<Map<String, Object>> list = getReport(reportParam);
        Workbook workbook = WorkbookFactory.create(true);
        Sheet sheet = workbook.createSheet();
        int rowIndex = 0;
        int cellIndex = 0;
        Row row = sheet.createRow(rowIndex++);
        row.setHeight((short) (25 * 20));
        Cell cell = row.createCell(cellIndex++);
        cell.setCellValue("洛阳公交公司退役军人优待证刷卡次数统计表(按线路)");
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,2));
        cell.setCellStyle(headerStyle);
        row.createCell(cellIndex++).setCellStyle(headerStyle);
        row.createCell(cellIndex++).setCellStyle(headerStyle);

        cellIndex = 0;
        row = sheet.createRow(rowIndex ++);
        row.setHeight((short) (25 * 20));
        cell = row.createCell(cellIndex++);
        cell.setCellValue("数据日期: " + reportParam.getStartDate() + "-" + reportParam.getEndDate());
        CellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setAlignment(HorizontalAlignment.LEFT);
        dateStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        dateStyle.setBorderBottom(BorderStyle.THIN);
        dateStyle.setBorderLeft(BorderStyle.THIN);
        dateStyle.setBorderRight(BorderStyle.THIN);
        dateStyle.setBorderTop(BorderStyle.THIN);
        sheet.addMergedRegion(new CellRangeAddress(1,1,0,2));
        cell.setCellStyle(dateStyle);
        row.createCell(cellIndex++).setCellStyle(dateStyle);
        row.createCell(cellIndex++).setCellStyle(dateStyle);

        cellIndex = 0;
        row = sheet.createRow(rowIndex ++);
        row.setHeight((short) (18 * 20));
        cell = row.createCell(cellIndex++);
        cell.setCellValue("序号");
        cell.setCellStyle(dateStyle);
        cell = row.createCell(cellIndex++);
        cell.setCellValue("线路号");
        cell.setCellStyle(dateStyle);
        cell = row.createCell(cellIndex++);
        cell.setCellValue("交易金额");
        cell.setCellStyle(dateStyle);
        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> map = list.get(i);
            cellIndex = 0;
            row = sheet.createRow(rowIndex ++);
            row.setHeight((short) (18 * 20));
            cell = row.createCell(cellIndex++);
            cell.setCellValue(i+1);
            cell.setCellStyle(dateStyle);
            cell = row.createCell(cellIndex++);
            cell.setCellValue((String) map.get("lineNo"));
            cell.setCellStyle(dateStyle);
            cell = row.createCell(cellIndex++);
            cell.setCellValue(((BigDecimal)map.get("takeCount")).stripTrailingZeros().toPlainString());
            cell.setCellStyle(dateStyle);
        }

        cellIndex = 0;
        row = sheet.createRow(rowIndex ++);
        row.setHeight((short) (18 * 20));
        cell = row.createCell(cellIndex++);
        cell.setCellValue("合计");
        cell.setCellStyle(dateStyle);
        cell = row.createCell(cellIndex++);
        cell.setCellValue(list.size());
        cell.setCellStyle(dateStyle);

        cell = row.createCell(cellIndex++);
        BigDecimal result = BigDecimal.ZERO;
        for (Map<String, Object> map : list) {
            result = result.add((BigDecimal) map.get("takeCount"));
        }
        cell.setCellValue(result.stripTrailingZeros().toPlainString());
        cell.setCellStyle(dateStyle);
        sheet.setColumnWidth(0, 10 * 256);
        sheet.setColumnWidth(1, 38 * 256);
        sheet.setColumnWidth(2, 38 * 256);

        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        workbook.write(bos);
        workbook.close();
        byte[] byteArray = bos.toByteArray();
        bos.close();
        response.setHeader("Content-Type", "application/octet-stream");
        response.setIntHeader("Content-Length", byteArray.length);
        String filename = "洛阳公交公司退役军人优待证刷卡次数统计表(按线路)_" + reportParam.getStartDate()+"_" + reportParam.getEndDate()+".xlsx";
        response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(
                filename, StandardCharsets.UTF_8
        ));
        response.getOutputStream().write(byteArray);

    }
}
